hive中collect

2024-07-13 01:29| 来源: 网络整理| 查看: 265

文章目录 hive中collect_list和concat_ws函数对null值的处理1. 验证

hive中collect_list和concat_ws函数对null值的处理

!!! note 结论： - collect_list函数会过滤掉为null的值，然后将其余非空的值组合成list。如果数据值都为null，则结果为空数组。 - concat_ws(';',collect_list(col_name))，当col_name的值都为null时，collect_list结果为空数组，concat_ws的结果为’'空字符串，而不是null

1. 验证测试数据 CREATE TABLE table1(customer_no STRING, stock_fee double, advertisement_fee double); INSERT INTO table1 VALUES ("C0001", 0.1, 1221.12) ,("C0001", NULL, 1130.52) ,("C0001", 0.1, 1084.72) ,("C0001", NULL, 1738.32) ,("C0001", NULL, 1817.67) ,("C0001", NULL, 3271.45) ,("C0002", NULL, 2587.76) ,("C0002", NULL, 1397.60) ,("C0002", NULL, 2871.75) ,("C0002", NULL, 812.08) ,("C0002", NULL, 1643.39) ,("C0003", 2635.23, 2587.76) ,("C0003", 1253.69, 1397.60) ,("C0003", 253.69, 2871.75) ,("C0003", 1528.12, 812.08) ,("C0003", 412.53, 1643.39); 验证collect_list的结果 SELECT customer_no ,collect_list(cast(stock_fee as string)) FROM table1 WHERE customer_no = 'C0002' GROUP BY customer_no; -- 结果如下，为空数组，[]表示一个空数组 +--------------+------+ | customer_no | _c1 | +--------------+------+ | C0002 | [] | +--------------+------+ -- []的结果和如下结果相同，因此验证了上述结果是空数组 select array(); +------+ | _c0 | +------+ | [] | +------+

当值全部为null，结果为空数组

SELECT customer_no ,collect_list(cast(stock_fee as string)) FROM table1 WHERE customer_no = 'C0001' GROUP BY customer_no; -- 结果如下， +--------------+----------------+ | customer_no | _c1 | +--------------+----------------+ | C0001 | ["0.1","0.1"] | +--------------+----------------+

当值中既有null，又有非null值，结果为非null的集合

SELECT customer_no ,collect_list(cast(stock_fee as string)) FROM table1 WHERE customer_no = 'C0003' GROUP BY customer_no; -- 结果如下 +--------------+----------------------------------------------------+ | customer_no | _c1 | +--------------+----------------------------------------------------+ | C0003 | ["2635.23","1253.69","253.69","1528.12","412.53"] | +--------------+----------------------------------------------------+ 验证concat_ws的结果 SELECT customer_no ,concat_ws(';',collect_list(cast(stock_fee as string))) FROM table1 WHERE customer_no = 'C0001' GROUP BY customer_no; -- 结果如下 +--------------+----------+ | customer_no | _c1 | +--------------+----------+ | C0001 | 0.1;0.1 | +--------------+----------+ SELECT customer_no ,ccw ,if(ccw = '','T','F') AS is_empty ,if(ccw is null,'T','F') AS is_null FROM ( SELECT customer_no ,concat_ws(';',collect_list(cast(stock_fee AS string))) AS ccw FROM table1 WHERE customer_no = 'C0002' GROUP BY customer_no ) AS tmp; -- 结果如下 +--------------+------+-----------+----------+ | customer_no | ccw | is_empty | is_null | +--------------+------+-----------+----------+ | C0002 | | T | F | +--------------+------+-----------+----------+

如果collect_list的结果为空字符传，则concat_ws的结果为空字符串。

【本文地址】

公司简介

联系我们